package com.gemptc.wode.daoImp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.gemptc.bean.Photo_Library;
import com.gemptc.bean.Sciencebean;
import com.gemptc.bean.Travelsbean;
import com.gemptc.bean.UserBean;
import com.gemptc.daoimp.SelectImp;
import com.gemptc.utils.DBUtil;
import com.gemptc.wode.dao.ISelect_Wouser;

public class Select_WouserImp implements ISelect_Wouser {
	Connection connection = DBUtil.getConnection();
	PreparedStatement preparedStatement = null;
	ResultSet resultSet = null;

	// 查找被关注人的昵称和头像
	public List<UserBean> getUserbean(int user_id) {

		List<UserBean> UserBeanList = new ArrayList<UserBean>();
		String sql = "SELECT users.`user_name`, users.`user_nickname`, users.`user_imgPath` FROM users, regrad WHERE users.`user_id` = regrad.`focused_id` AND regrad.`user_id` = ? ;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, user_id);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				String username = resultSet.getString(1);
				String nickname = resultSet.getString(2);
				System.out.println(nickname);
				String user_imgPath = resultSet.getString(3);
				System.out.println(user_imgPath);
				UserBean UserBean = new UserBean(username, nickname,
						user_imgPath);
				UserBeanList.add(UserBean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return UserBeanList;
	}
	
	
	

	// 通过用户名 获取当前用户的昵称，性别，还有简介
	public List<UserBean> getUserBean1(String username) {

		List<UserBean> UserBeanList1 = new ArrayList<UserBean>();
		String sql = "SELECT user_nickname,user_gender,user_profile,user_imgPath FROM users where user_name=?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, username);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				String nickname = resultSet.getString(1);
				System.out.println(nickname);
				String user_gender = resultSet.getString(2);
				System.out.println(user_gender);
				String user_profile = resultSet.getString(3);
				String user_imgpath = resultSet.getString(4);
				System.out.println(user_profile);
				UserBean UserBean = new UserBean(username, nickname,
						user_gender, user_profile, user_imgpath);
				UserBeanList1.add(UserBean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return UserBeanList1;
	}

	// 修改用户的昵称，性别和简介：
	public boolean update(String user_name, String user_nickname,
			String user_gender, String user_profile) {
		boolean flag = false;
		String sql = "UPDATE users SET user_nickname=?,user_gender=?,user_profile=? WHERE user_name=?;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, user_nickname);
			System.out.println("用户名");
			preparedStatement.setString(2, user_gender);
			System.out.println("昵称");
			preparedStatement.setString(3, user_profile);
			System.out.println("性别");
			preparedStatement.setString(4, user_name);
			System.out.println("简介");
			preparedStatement.execute();
			System.out.println("操作成功");
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return flag;
	}

	//
	// 头像更新
	public boolean update1(String user_imgPath, int user_id) {
		boolean flag = false;
		String sql = "UPDATE users SET user_imgPath=? WHERE user_id=?;";
		try {
			System.err.println("修改中");
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, user_imgPath);
			System.out.println(user_imgPath);
			preparedStatement.setInt(2, user_id);
			System.out.println(user_id);
			preparedStatement.execute();
			System.out.println("操作成功");
			flag = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	// 根据username 查出userid
	public int selectuserid(String username) {

		int userid = 0;
		String sql = "SELECT user_id FROM users WHERE user_name=? limit 1;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, username);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				userid = resultSet.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return userid;
	}

	public boolean delect_focuse(String username, String focusename) {
		boolean falg = false;
		int userid = selectuserid(username);
		int focuseid = selectuserid(focusename);
		String sql = "DELETE  FROM regrad WHERE user_id=? AND focused_id=?;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, userid);
			preparedStatement.setInt(2, focuseid);
			preparedStatement.execute();
			falg = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return falg;
	}

	// 删除收藏的推荐科普
	public boolean delect_shoucang(int s_id, int user_id) {
		boolean falg = false;

		String sql = "DELETE  FROM collect WHERE s_id=? AND user_id=?;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, s_id);
			preparedStatement.setInt(2, user_id);
			preparedStatement.execute();
			falg = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return falg;
	}

	// 删除我的游记
	public boolean delect_youji(int t_id, int user_id) {
		boolean falg = false;
		System.out.println(t_id);
		System.out.println(user_id);
		String sql = "DELETE  FROM travels WHERE t_id=? AND user_id=?;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, t_id);
			preparedStatement.setInt(2, user_id);
			preparedStatement.execute();
			System.out.println("aaaa");
			falg = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return falg;
	}

	// 根据Collect（收藏表）的id查找Science（科普推荐表）中的标题和时间,还有图片

	public List<Sciencebean> seleteshoucang(int user_id) {

		List<Sciencebean> scienceList = new ArrayList<Sciencebean>();
		String sql = "SELECT science.`s_title`,science.`s_upload`,"
				+ "science.`s_photo_path`,science.`s_id`,"
				+ "science.`s_content`,science.`s_photo_jianjie`,science.`s_type` "
				+ "FROM science,collect WHERE "
				+ "science.`s_id`=collect.`s_id` " + "AND collect.`user_id`=?;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, user_id);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				String s_title = resultSet.getString(1);
				String s_upload = resultSet.getString(2);
				System.out.println(s_upload);
				String s_photo_path = resultSet.getString(3);
				System.out.println(s_photo_path);
				int s_id = resultSet.getInt(4);
				String s_content = resultSet.getString(5);
				String s_photo_jianjie = resultSet.getString(6);
				int s_type = resultSet.getInt(7);
				Sciencebean sciencebean = new Sciencebean(s_id, s_title,
						s_content, s_upload, s_type, s_photo_path,
						s_photo_jianjie);
				/*
				 * Sciencebean sciencebean = new Sciencebean(s_title, s_upload,
				 * s_photo_path,s_id,s_content,s_photo_jianjie,s_type);
				 */
				scienceList.add(sciencebean);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return scienceList;
	}

	// 根据Collect（收藏表）的id查找 Travels(游记表)中的标题和时间，图片
	public List<Travelsbean> seleteyoujishoucang(int user_id) {
		Connection con = DBUtil.getConnection();
		PreparedStatement pre = null;
		ResultSet res = null;
		SelectImp selectImp=new SelectImp();
		List<Travelsbean> travelsList = new ArrayList<Travelsbean>();
		List<Photo_Library> list;
		String sql = "SELECT travels.`t_id`,travels.`t_title`,travels.`t_time`,travels.`rp_id`,travels.`t_content`,travels.`t_hidden`,travels.`user_id` FROM travels,collect WHERE travels.`t_id`=collect.`t_id` AND collect.`user_id`=?;";

		try {
			pre = con.prepareStatement(sql);
			pre.setInt(1, user_id);
			res = pre.executeQuery();
			while (res.next()) {
				int tid = res.getInt(1);
				String t_title = res.getString(2);
				String t_time = res.getString(3);
				int rp_id = res.getInt(4);
				String t_content = res.getString(5);
				boolean t_hidden = res.getBoolean(6);
				int user_id1=res.getInt(7);
				list = selectphoto(tid);
				String user_nickname=selectImp.select(user_id1);
				Travelsbean travelsbean = new Travelsbean(tid, user_id1, user_nickname,rp_id,t_title,
						t_time, t_content, t_hidden, list);
				/*
				 * Travelsbean travelsbean = new Travelsbean(tid, t_title,
				 * t_time, list);
				 */
				travelsList.add(travelsbean);
				System.out.println(travelsList);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(res, pre, con);
		}

		return travelsList;
	}

	// 114查我自己的游记所有属性
	public List<Travelsbean> seletemyouji(int user_id) {
		Connection con = DBUtil.getConnection();
		PreparedStatement pre = null;
		ResultSet res = null;
		SelectImp selectImp=new SelectImp();
		List<Travelsbean> travelsList = new ArrayList<Travelsbean>();
		List<Photo_Library> list;
		String sql = "SELECT*from  travels where user_id=?;";

		try {
			pre = con.prepareStatement(sql);
			pre.setInt(1, user_id);
			res = pre.executeQuery();
			while (res.next()) {
				int tid = res.getInt(1);
				int user_id1=res.getInt(2);
				int rp_id = res.getInt(3);
				String t_title = res.getString(4);
				String t_time = res.getString(5);
				String t_content = res.getString(6);
				boolean t_hidden = res.getBoolean(7);
				list = selectphoto(tid);
				String user_nickname=selectImp.select(user_id1);
				String user_imgPath=selectImp.select1(user_id1);
				String username=selectImp.selectUser_name(user_id1);
				Travelsbean travelsbean = new Travelsbean(
						tid, user_id1, username,
						user_nickname, rp_id, t_title,
						t_time, t_content, t_hidden, 
						user_imgPath, list);
				travelsList.add(travelsbean);
				System.out.println(travelsList);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(res, pre, con);
		}

		return travelsList;
	}

	/*
	 * // 根据查找 Travels(游记表)中的标题和时间，图片 public List<Travelsbean>
	 * seletewodeyouji(int user_id) { Connection con = DBUtil.getConnection();
	 * PreparedStatement pre = null; ResultSet res = null; List<Travelsbean>
	 * travelsList = new ArrayList<Travelsbean>(); List<Photo_Library> list;
	 * String sql =
	 * "SELECT travels.`t_id`,travels.`t_title`,travels.`t_time` FROM travels WHERE travels.`user_id`=?;"
	 * ; try { pre = con.prepareStatement(sql); pre.setInt(1, user_id); res =
	 * pre.executeQuery(); while (res.next()) { int tid = res.getInt(1); String
	 * t_title = res.getString(2); String t_time = res.getString(3);
	 * System.out.println(tid); list = selectphoto(tid);
	 * System.out.println(list); Travelsbean travelsbean = new Travelsbean(tid,
	 * t_title, t_time, list);
	 * 
	 * travelsList.add(travelsbean); System.out.println(travelsList);
	 * 
	 * } } catch (SQLException e) { // TODO Auto-generated catch block
	 * e.printStackTrace(); } finally { DBUtil.release(res, pre, con); }
	 * 
	 * return travelsList; }
	 */

	public List<Photo_Library> selectphoto(int t_id) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement prep = null;
		ResultSet resu = null;

		List<Photo_Library> list = new ArrayList<Photo_Library>();
		String sql = "SELECT pl_path FROM photo_library WHERE t_id=?;";
		try {
			prep = conn.prepareStatement(sql);
			prep.setInt(1, t_id);
			resu = prep.executeQuery();
			while (resu.next()) {
				String pl_path = resu.getString(1);
				Photo_Library pl = new Photo_Library(pl_path);
				list.add(pl);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(resu, prep, conn);
		}

		return list;
	}

	/*
	 * public List<UserBean> getusername(int user_id) {
	 * 
	 * List<UserBean> userbeanList = new ArrayList<UserBean>(); String sql =
	 * "SELECT user_name FROM users WHERE user_id=?;"; try { preparedStatement =
	 * connection.prepareStatement(sql); preparedStatement.setInt(1, user_id);
	 * resultSet = preparedStatement.executeQuery(); while (resultSet.next()) {
	 * String username = resultSet.getString(1); System.out.println(username);
	 * 
	 * UserBean userbean = new UserBean(username); userbeanList.add(userbean);
	 * 
	 * } } catch (SQLException e) { // TODO Auto-generated catch block
	 * e.printStackTrace(); } finally { DBUtil.release(resultSet,
	 * preparedStatement, connection); } return userbeanList;
	 * 
	 * }
	 */
}
